<?php
class Muser extends CI_Model{
   
    private $_table = "SHU";
    
    function __contruct(){
        parent::__construct();
        $this->load->database();		
		$this->load->library('my_time');
		$this->load->helper('date');
    }

    //--- Lay du lieu
    function getalldata($table,$off="",$limit=""){
        $this->db->select('*');
        $this->db->from($table);
        $this->db->limit($off,$limit);
        $query = $this->db->get();
        $data = $query->result();
        return $data;
    }
    
	function getAMonthTime($FromDate,$ToDate,$off="",$limit=""){
		$this->db->select('*');
		$this->db->where('FromDate',$FromDate);
		$this->db->where('ToDate',$ToDate);
		$this->db->limit($off,$limit);
		$query=$this->db->get('MonthTime');
		return $query->result();
		
	}
	 
	function getAllContract($off="",$limit=""){
		$this->db->select('Contract.*');
		$this->db->from('Contract');
		$this->db->join('Staff','Contract.Staff_ID=Staff.Staff_ID');
		$this->db->select('FullName');
		$this->db->limit($off,$limit);
		$query=$this->db->get();
		return $query->result();
	} 

    //--- Them User moi
    function addData($table,$data){
        if($this->db->insert($table,$data))
            return TRUE;
        else
            return FALSE;
    }
	
	// Get max Training_ID
	function getMaxId(){
		$query =  $this->db->query('SELECT MAX(Training_ID) FROM  Training');
		return $query->row(0);
	}
	
	//check insert
	function  checkInsert($StaffID,$Date){
		$this->db->where('Staff_ID',$StaffID);
		$this->db->where('Date',$Date);
		$query=$this->db->get('CustomerReport');
		if ($query->num_rows()>0){
			return FALSE;
		}
		else {
			return TRUE;
		}
	}
	
  // Del Column in table
   function delColumn($table,$NameID,$id)
   {   
     	$this->db->where($NameID,$id);            	      	
		$this->db->delete($table);	
			        
    }
  // get all data from table	
	
	function getData($table)
	{					
		$query=$this->db->get($table);
		if ($query->num_rows()>0){
			return $query->result();	
		}else{
			return '0';
		}
		
	}
	// get a row 	
	function getRdata($table,$NameID,$data)
	{
		$this->db->where($NameID,$data);			
		$query=$this->db->get($table);
		return $query->row();
	}
	
	function getNdata($table,$NameID,$data){
		$this->db->distinct();
		$this->db->where($NameID,$data);
		$query=$this->db->get($table);
		return $query->result();
	}
	//test
	function getMdata($table){
		$this->db->distinct();		
		$query=$this->db->get($table);
		return $query;
	}
	//check Data xem mot ID da ton tai hay chua
	function checkData($table,$NameID,$data){
		$this->db->where($NameID,$data);
		$query=$this->db->get($table);	
		
		if ($query->num_rows()>0){
			return FALSE;
		}
		else {
			return TRUE;
		}
	}
	
    //--- Cap nhat user
    function updateData($table,$idName,$id,$data){
        $this->db->where($idName,$id);
        if($this->db->update($table,$data))
            return TRUE;
        else
            return FALSE;
    }

    // Tong so record
    function num_rows($table){
        return $this->db->count_all($table);
    } 
   
   // get SHU
 	function getSHU()
   {
      	$this->db->select('*');
		$this->db->order_by('EffectiveDate', 'DESC'); 
		$this->db->limit(1);
		$query = $this->db->get('SHU');
		return $query->row();
	   
   }
   
   function getMax($table){
   		$this->db->select('*');
		$this->db->order_by('EffectiveDate', 'DESC'); 
		$this->db->limit(1);
		$query = $this->db->get($table);
		return $query->row();
   }
   
    function getOverTime(){
   		$this->db->select('*');
		$this->db->where('Type','T');
		$this->db->order_by('EffectiveDate', 'DESC'); 
		$this->db->limit(1);
		$query = $this->db->get('OverTime');
		return $query->row();
   }
   
   function getOT($OverTime_ID){
   		$this->db->where('Type','N');
		$this->db->where('OverTime_ID',$OverTime_ID);
		$query=$this->db->get('OverTime');
		if ($query->num_rows()>0)
		{
			return $query->row();
		}else{
			return '0';
		}
   }
   
   function getTax(){
   		$row=$this->getMax('Tax');
		$EffectiveDate=$row->EffectiveDate;
		$this->db->where('EffectiveDate',$EffectiveDate);
		$this->db->order_by('From','ASC');
		$query=$this->db->get('Tax');
		return $query->result();
   }
   
   function getUnpaid($id){
   		//get number of dependent	
   		$this->db->where('Staff_ID',$id);
		$query=$this->db->get('Staff');
		$this->db->limit(1);
		$row=$query->row();		
		$n=$row->NumberOfDependent;
		//get PIT
		$this->db->select('*');
		$this->db->order_by('EffectiveDate', 'DESC'); 
		$this->db->limit(1);
		$query = $this->db->get('PIT');
		$result=$query->row();
		$amount = $result->IndividualDeduction+$result->Dependent * $n;
		return $amount;
    }
   
   function getTime($Staff_ID,$FromDate,$ToDate){
   	 $this->db->where('Staff_ID',$Staff_ID);
	 $this->db->where('FromDate',$FromDate);
	 $this->db->where('ToDate',$ToDate);
	 $this->db->limit(1);
	 $query=$this->db->get('MonthTime');
	 $row=$query->row();
	 return $row;
   }
 //get basic salary in contract  
   function getBasic($Staff_ID){   		
		$this->db->where('Staff_ID',$Staff_ID);
		$this->db->order_by('JoiningDate', 'DESC'); 
		$this->db->limit(1);
		$query = $this->db->get('Contract');
		$row=$query->row();		
		if ($query->num_rows()>0){
			return $row->BasicSalary;
		}
		else {
			return 0;
		}
		
   } 
  // get Contract
  
  function getContract($Staff_ID,$FromDate,$ToDate){
  	$array=array(
		'EffectiveDate <'=> $ToDate,
		'ExpiredDate >'=>$FromDate
	);
	$array1=array(
		'EffectiveDate <'=> $ToDate,
		'ContractType_ID'=>'LC-IC'
	);
			  	  
  		$this->db->where('Staff_ID',$Staff_ID);
		$this->db->where('EffectiveDate <',$ToDate);
		$this->db->where('ExpiredDate >',$FromDate);
		//$this->db->where($array);
		//$this->db->where($array1);		
		$this->db->order_by('EffectiveDate','ASC');
		$query=$this->db->get('Contract');		
		if ($query->num_rows()>0){
			return $query;
		}
		return 0;
  }	
  
  function getAnnex($Contract_ID){
  		$this->db->where('Contract_ID',$Contract_ID);
		$this->db->order_by('AnnexDate','DESC');
		$this->db->limit(1);
		$query=$this->db->get('Annex');
		if ($query->num_rows() >0){
			return $query->row();
		}else{
			return '0';
		}
  }
       
  //get basic salary in lab staff
   
   function getAllowance($Staff_ID,$day){
   		$this->db->where('Staff_ID',$Staff_ID);
		$this->db->where('ActiveDate <',$day);		
		$query=$this->db->get('MAS');
		$result=$query->result();
		$t=0;
		foreach ($result as $row){
			$row1=$this->getRdata('MonthlyAllow','MonthlyAllow_ID',$row->MonthlyAllow_ID);
			$t=$row1->Amount+$t;
		}
		return $t;;
   } 
   
   function getAllStaff($fromdate,$todate){
   		$this->db->select('Staff_ID','BasicSalary','DayNight');
   		$this->db->where('EffectiveDate <', $todate);	
   		$this->db->where('ExitDate >',$fromdate);
		$query=$this->db->get('Contract');
		return $query->result();	
		
   }
   function checkWorkDay($Staff_ID,$Date){
   		$this->db->where('Staff_ID',$Staff_ID);
		$this->db->where('Date',$Date);
		$query=$this->db->get('WorkDay');
		if ($query->num_rows()>0){
			return FALSE;
		}
		else {
			return TRUE;
		}
	
   }
   function getWorkDay($Staff_ID,$Date){
   	 $this->db->where('Staff_ID',$Staff_ID);
	 $this->db->where('Date',$Date);
	 $query=$this->db->get('WorkDay');
	 if ($query->num_rows()>0){
			return $query->row();
		}
	 else{
	 	return NULL;
	 }
   }
   function getMonthTime($Staff_ID,$start,$end){
   		$this->db->where('Staff_ID',$Staff_ID);
		$this->db->where('Date >=',$start);
		$this->db->where('Date <=',$end);
		$query=$this->db->get('WorkDay');
		if ($query->num_rows() > 0){
			return $query->result();
		}else{
			return NULL;
		}
   }
   function checkMonthTime($Staff_ID,$start,$end){
   		$this->db->where('Staff_ID',$Staff_ID);
		$this->db->where('FromDate',$start);
		$this->db->where('ToDate',$end);
		$query=$this->db->get('MonthTime');
		if ($query->num_rows()>0){
			return FALSE;
		}
		else {
			return TRUE;
		}
   }
   function getRMonthTime($Staff_ID,$start,$end){
   		$this->db->where('Staff_ID',$Staff_ID);
		$this->db->where('FromDate',$start);
		$this->db->where('ToDate',$end);
		$query=$this->db->get('MonthTime');
		if ($query->num_rows()>0){
			return $query->row();
		}else{
			$this->my_time->makeMonthTime($Staff_ID,$start,$end);
			return $this->getRMonthTime($Staff_ID, $start, $end);
		}
		
   }
   
   // check contract
   function checkContract($Staff_ID,$Date){
   		$this->db->where('Staff_ID',$Staff_ID);
		$this->db->where('ExpiredDate >=',$Date);
		$query=$this->db->get('Contract');
		if ($query->num_rows()>0){
			return FALSE;
		}
		else {
			return TRUE;
		}
   }
   function getWorkingDay($start,$end){
		$first=strtotime($start);
		$last=strtotime($end);
		$kc=abs($last - $first)/86400;	
		$i=0;	
		while(date("w", strtotime($start)+$i*86400)!='0'){
			$i=$i+1;
		}
		if ($i>$kc) return $kc;
		
		$n=(int)(($kc-$i)/7);
		
		return ($kc-$n);	
	}
   function getT7($start,$end){
   		$first=strtotime($start);
		$last=strtotime($end);
		$kc=abs($last - $first)/86400;	
		$i=0;	
		while(date("w", strtotime($start)+$i*86400)!='6'){
			$i=$i+1;
		}		
		$n=(int)(($kc-$i)/7);
	 	return $n+1;	
   }
   
   
   function getStaffID(){
   		$this->db->select('Staff_ID');
		$query=$this->db->get('Staff');
		return $query->result();
   }
   
   function getSalary($FromDate,$ToDate){
   		$this->db->where('FromDate',$FromDate);
		$this->db->where('ToDate',$ToDate);
		$query=$this->db->get('Salary');
		return $query->result();
   }
   function checkSalary($Staff_ID,$FromDate,$ToDate){
   		$this->db->where('FromDate',$FromDate);
		$this->db->where('Staff_ID',$Staff_ID);
		$this->db->where('ToDate',$ToDate);
		$query=$this->db->get('Salary');
		if ($query->num_rows()>0){
			return FALSE;
		}
		else {
			return TRUE;
		}
   }
   
   function getStaffSalary($Staff_ID,$FromDate,$ToDate){
   		$this->db->where('Staff_ID',$Staff_ID);
		$this->db->where('FromDate',$FromDate);
		$this->db->where('ToDate',$ToDate);
		$query=$this->db->get('Salary');
		if($query->num_rows()>0) {
			return $query->row();
		}else{
			return '0';
		}
		
   }
	function getSalaryID($Staff_ID,$FromDate,$ToDate){
   		$this->db->select('Salary_ID');	
   		$this->db->where('FromDate',$FromDate);
		$this->db->where('Staff_ID',$Staff_ID);
		$this->db->where('ToDate',$ToDate);
		$query=$this->db->get('Salary');
		return $query->row();
	}
   // get staff in lap
   
   function getStaffInLab($Lab_ID){
   		$this->db->where('Lab_ID',$Lab_ID);
		$this->db->distinct();
		$query=$this->db->get('LabStaff');
		if ($query->num_rows()>0){
			return $query->result();
		}else{
			return '0';
		}
   }
   function getLabSalary($Lab_ID,$FromDate,$ToDate){
   		$this->db->where('Lab_ID',$Lab_ID);
		$this->db->where('FromDate',$FromDate);
		$this->db->where('ToDate',$ToDate);
		$query=$this->db->get('CustomerReport');
		if ($query->num_rows()>0){
			return $query->result();
		}else{
			return '0';
		}
		
   }
   
   function checkCustomerReport($Lab_ID,$Staff_ID,$FromDate,$ToDate){
   		$this->db->where('Lab_ID',$Lab_ID);
		$this->db->where('Staff_ID',$Staff_ID);
		$this->db->where('FromDate',$FromDate);
		$this->db->where('ToDate',$ToDate);
		$query=$this->db->get('CustomerReport');
		if ($query->num_rows()>0){
			return $query->row();
		}else{
			return '0';
		}
   }
   function getMSalary($FromDate,$ToDate){
   		$this->db->where('FromDate',$FromDate);
		$this->db->where('ToDate',$ToDate);
		$query=$this->db->get('Salary');		
		return $query;
   }
   
   function getCustomerReport($Lab_ID,$FromDate,$ToDate){
	 	$this->db->where('Lab_ID',$Lab_ID);
		$this->db->where('FromDate',$FromDate);
		$this->db->where('ToDate',$ToDate);
		$query=$this->db->get('CustomerReport');
		
		return $query;	
   }
   
   function getWorkLevel($WorkLevel_ID){
   		$this->db->where('WorkLevel_ID',$WorkLevel_ID);
		$query=$this->db->get('WorkLevel');
		if ($query->num_rows()>0)
		{
			return $query->row();
		}
   }
   
   // Delete Staff Training row
   function delStaffTraining($trainingId,$staffId)
    {
    	$this->db->where('Training_ID', $trainingId);
     	$this->db->where('Staff_ID', $staffId);            	      	
		$this->db->delete('StaffTraining');		        
    }
	
	// Update Staff Training information
	function updateStaffTraining($trainingId, $staffId, $data)
    {
    	$this->db->where('Training_ID', $trainingId);
     	$this->db->where('Staff_ID', $staffId);            	      	
		if($this->db->update('StaffTraining', $data))
			return TRUE;
        else
            return FALSE;	        
    }
	
	// Check if Staff Training record existed
	function isStaffTrainingExisted($trainingId, $staffId){
		$this->db->where('Training_ID',$trainingId);
		$this->db->where('Staff_ID',$staffId);
		$query=$this->db->get('StaffTraining');
		if ($query->num_rows()>0){
			return TRUE;
		}
		else {
			return FALSE;
		}
	}
}
?>
